


***************************
** Step 1: load PUOUT data from IMLS, 2013 to 2019, to get library system info:

clear
	set obs 1
	generate X = 1 in 1
	save $datapath/temp/outlet.dta, replace 

forvalues k=13(1) 19 {
	import delimited $datapath/Puout`k'a.csv, clear

	gen year=2000+`k'
	append using $datapath/temp/outlet.dta, force
	save $datapath/temp/outlet.dta, replace 
}

drop if X==1

	collapse (sum) sq_feet (count) nlib=sq_feet,  by(fscskey year)
	save  $datapath/intermediate/sq_footage.dta, replace 
	erase $datapath/temp/outlet.dta
	
*******************************


*******************************
** Step 2: load PUPLD data from IMLS, 2013 to 2019, to get circ, holdings, etc.:

clear
	set obs 1
	generate X = 1 in 1
	save $datapath/temp/outlet.dta, replace 

forvalues k=13(1) 19 {
	import delimited $datapath/pupld`k'a.csv, clear

	gen year=2000+`k'
	append using $datapath/temp/outlet.dta, force
	save $datapath/temp/outlet.dta, replace 
}	
	erase $datapath/temp/outlet.dta

	drop if X==1
	keep if elmatcir>0 

	egen lno=group(fscskey)

*** create circulation data:

	replace totcir=. if totcir<0 
	replace elmatcir=. if elmatcir< 0 
	
	gen Qp = totcir - elmatcir
	gen Qe = elmatcir	

*** create per-capita visits: 

	gen vpc = visits/popu_lsa 

*** create holdings data:

	gen Np = bkvol + audio_ph + video_ph
	gen Ne = ebook + audio_dl + video_dl
*******************************
	
	
*******************************
** combine data 

merge 1:1 fscskey year  using $datapath/intermediate/sq_footage.dta, nogen

	gen city_state = city + ", " + stabr
	egen urbanness = min(reaplocale_mod), by(lno)

*******************************


*******************************
** clean location identifiers  
	
	keep if Qp>0 & Np>0 & Qp~=.

	egen xfp=max( fipsplac), by(lno)
	egen xfs=max( fipsst), by(lno)
	drop fipsst fipsplac
	rename xfp fipsplac 
	rename xfs fipsst 
	

*** final tweaks:

	drop if lno==.

	keep if Np>=0 & Ne>=0 & Qp>=0 & Qe>=0 & prmatexp>=0 & elmatexp>=0 & visits>0 & popu_lsa>0 & bkvol>=0
	keep if Ne<5000000

	
	keep Qp Qe Np Ne popu_lsa lno year fipsst fipsplac visits stabr sq_feet nlib prmatexp elmatexp othmatex totexpco fscskey totstaff staffexp totpro  loanto loanfm libname urbanness  master libraria othpaid kidcircl kidpro staffexp totstaff totpro totatten hrs_open centlib branlib bkmob totopexp subscrip yapro yaatten webvisit kidatten

*******************************

		
*******************************
** save main dataset
	
	save $datapath/clean/main_data.dta, replace





	
